package soldesk.logon;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;
import java.util.Vector;

import soldesk.board.BoardDataBean;

public class LogonDBBean {
	//SingleTon Pattern : LogonDBBean  m = LogonDBBean.getInstance();
	private static LogonDBBean instance = new LogonDBBean();
	
	public static LogonDBBean getInstance(){
		return instance;
	}
	
	// private 
	private LogonDBBean(){
		
	}
	
	private Connection getConnection() throws Exception {
		String jdbcDriver = "jdbc:apache:commons:dbcp:/pool";
		return DriverManager.getConnection(jdbcDriver);
	}
	// inputPro.jsp
	public void insertMember(LogonDataBean member) throws Exception {
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		try{
			conn = getConnection();
			
			pstmt = conn.prepareStatement(
					"insert into MEMBERS(id,passwd,name,jumin1,jumin2,email,blog,reg_date,zipcode,address)"
					+ " values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)" );
			
			pstmt.setString(1,  member.getId() );
			pstmt.setString(2,  member.getPasswd());
			pstmt.setString(3, member.getName());
			pstmt.setString(4,  member.getJumin1());
			pstmt.setString(5, member.getJumin2());
			pstmt.setString(6, member.getEmail());
			pstmt.setString(7, member.getBlog());
			pstmt.setTimestamp(8, member.getReg_date());
			pstmt.setString(9,member.getZipcode());
			pstmt.setString(10, member.getAddress());
			pstmt.executeUpdate();
		} catch(Exception ex){
			ex.printStackTrace();
		} finally {
			if( pstmt != null ) try{ pstmt.close(); } catch(SQLException ex) {}
			if( conn != null ) try{ conn.close(); } catch(SQLException ex) {}
		}
	}
	
	public Vector zipcodeRead(String area3) {
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Vector vecList = new Vector();
		try{
			con = getConnection();
			String strQuery = "select * from zipcode where area3 like '" + area3 + "%'";
			pstmt = con.prepareStatement(strQuery);
			rs = pstmt.executeQuery();
			while( rs.next() ) {
				ZipcodeBean tempZipcode = new ZipcodeBean();
				tempZipcode.setZipcode(rs.getString("zipcode"));
				tempZipcode.setArea1(rs.getString("area1"));
				tempZipcode.setArea2(rs.getString("area2"));
				tempZipcode.setArea3(rs.getString("area3"));
				tempZipcode.setArea4(rs.getString("area4"));
				vecList.addElement(tempZipcode);
			}
		} catch(Exception ex) {
			System.out.println("Exception" + ex);
		} finally {
			if( rs != null ) try { rs.close(); } catch(SQLException ex){}
			if( pstmt != null ) try { pstmt.close(); } catch(SQLException ex){}
			if( con != null ) try { con.close(); } catch(SQLException ex){}
		}
		return vecList;
	}
	//loginPro.jsp
	public int userCheck(String id, String passwd) throws Exception {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String dbpasswd = "";
		int x = -1;
		
		try{
			conn = getConnection();
			
			pstmt = conn.prepareStatement(
					"select passwd from MEMBERS where id = ?" );
			
			pstmt.setString(1,  id);
			rs = pstmt.executeQuery();
			
			if( rs.next()) {
				dbpasswd = rs.getString("passwd");
				if(dbpasswd.equals(passwd))
						x = 1; // 
				else	
						x = 0; //
			} else
					x = -1;   // 
					
		} catch(Exception ex) {
			ex.printStackTrace();
		} finally {
			if ( rs != null ) try { rs.close(); } catch(SQLException ex) {}
			if( pstmt != null ) try { pstmt.close(); } catch(SQLException ex) {}
			if( conn != null ) try { conn.close(); } catch(SQLException ex) {}
		}
		return x;
	}
	
	// confirmId.jsp
	public int confirmId(String id) throws Exception {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String dbpasswd = "";
		int x = -1;
		
		try{
			conn = getConnection();
			
			pstmt = conn.prepareStatement("select id from MEMBERS where id = ?");
			pstmt.setString(1,  id);
			rs = pstmt.executeQuery();
			
			if(rs.next() )
				x = 1;		// 
			else 
				x = -1;	// 
		} catch(Exception ex) {
			ex.printStackTrace();
		} finally {
			if( rs != null ) try { rs.close(); } catch(SQLException ex) {}
			if( pstmt != null ) try { pstmt.close(); } catch(SQLException ex) {}
			if( conn != null ) try { conn.close(); } catch(SQLException ex) {}
		}
		
		return x;
	}
	
	public List getMemberArticles(int start, int end) throws Exception {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		LogonDataBean member = null;
		List memberList = null;
		try{
			conn = getConnection();
			
			pstmt = conn.prepareStatement(
					      "select id,name,passwd,jumin1,jumin2,email,blog,reg_date,zipcode,address ,r " +
					"from (select id,name,passwd,jumin1,jumin2,email,blog,reg_date,zipcode,address ,rownum r " +
					"from (select id,name,passwd,jumin1,jumin2,email,blog,reg_date,zipcode,address " +
					"from members )) where r >=? and r <=?" );
			pstmt.setInt(1,  start);
			pstmt.setInt(2, end);
			rs = pstmt.executeQuery(); 
			
			if( rs.next() ) {
				memberList = new ArrayList();
				do{
				member = new LogonDataBean();
				member.setId(rs.getString("id"));
				member.setPasswd(rs.getString("passwd"));
				member.setName(rs.getString("name"));
				member.setJumin1(rs.getString("jumin1"));
				member.setJumin2(rs.getString("jumin2"));
				member.setEmail(rs.getString("email"));
				member.setBlog(rs.getString("blog"));
				member.setReg_date(rs.getTimestamp("reg_date"));
				member.setZipcode(rs.getString("zipcode"));
				member.setAddress(rs.getString("address"));
				
				memberList.add(member);
				} while(rs.next());
			}
		} catch(Exception ex) {
			ex.printStackTrace();
		} finally {
			if( rs != null ) try { rs.close(); } catch(SQLException ex) {}
			if( pstmt != null ) try { pstmt.close(); } catch(SQLException ex) {}
			if( conn != null ) try { conn.close(); } catch(SQLException ex) {}
		}
		return memberList;
		
	}
		
	public List getMemberList() throws Exception {	// 
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		LogonDataBean member = null;
		List memberList = null;
		
		try{
			conn = getConnection();
			
			pstmt = conn.prepareStatement("select * from MEMBERS ");
			rs = pstmt.executeQuery();
			
			if( rs.next() ) {
				memberList = new ArrayList();
				do{
				member = new LogonDataBean();
				member.setId(rs.getString("id"));
				member.setPasswd(rs.getString("passwd"));
				member.setName(rs.getString("name"));
				member.setJumin1(rs.getString("jumin1"));
				member.setJumin2(rs.getString("jumin2"));
				member.setEmail(rs.getString("email"));
				member.setBlog(rs.getString("blog"));
				member.setReg_date(rs.getTimestamp("reg_date"));
				member.setZipcode(rs.getString("zipcode"));
				member.setAddress(rs.getString("address"));
				
				memberList.add(member);
				} while(rs.next());
			}
		} catch(Exception ex) {
			ex.printStackTrace();
		} finally {
			if( rs != null ) try { rs.close(); } catch(SQLException ex) {}
			if( pstmt != null ) try { pstmt.close(); } catch(SQLException ex) {}
			if( conn != null ) try { conn.close(); } catch(SQLException ex) {}
		}
		return memberList;
	}
	
	public int selectCount(){		// 
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		int x = 0;
		
		try{
			conn = getConnection();
			
			pstmt = conn.prepareStatement("select count(*) from members");
			rs = pstmt.executeQuery();
			
			if( rs.next() ){
				x = rs.getInt(1);
			}
		} catch(Exception ex){
			ex.printStackTrace();
		} finally {
			if( rs != null ) try { rs.close(); } catch (SQLException ex) {}
			if( pstmt != null ) try { pstmt.close(); } catch (SQLException ex) {}
			if( conn != null ) try { conn.close(); } catch (SQLException ex) {}
		}
		return x;
	}
		
	// modifyForm.jsp
	public LogonDataBean getMember(String id) throws Exception {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		LogonDataBean member = null;
		
		try{
			conn = getConnection();
			
			pstmt = conn.prepareStatement("select * from MEMBERS where id = ?");
			pstmt.setString(1, id);
			rs = pstmt.executeQuery();
			
			if( rs.next() ) {
				member = new LogonDataBean();
				member.setId(rs.getString("id"));
				member.setPasswd(rs.getString("passwd"));
				member.setName(rs.getString("name"));
				member.setJumin1(rs.getString("jumin1"));
				member.setJumin2(rs.getString("jumin2"));
				member.setEmail(rs.getString("email"));
				member.setBlog(rs.getString("blog"));
				member.setReg_date(rs.getTimestamp("reg_date"));
				member.setZipcode(rs.getString("zipcode"));
				member.setAddress(rs.getString("address"));
			}
		} catch(Exception ex) {
			ex.printStackTrace();
		} finally {
			if( rs != null ) try { rs.close(); } catch(SQLException ex) {}
			if( pstmt != null ) try { pstmt.close(); } catch(SQLException ex) {}
			if( conn != null ) try { conn.close(); } catch(SQLException ex) {}
		}
		return member;
	}
	// getMember() 
	public LogonDataBean getMember(String name, String jumin2) throws Exception {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		LogonDataBean member = null;
		
		try{
			conn = getConnection();
			
			pstmt = conn.prepareStatement("select * from MEMBERS where name = ? and jumin2 = ? ");
			pstmt.setString(1, name);
			pstmt.setString(2, jumin2);
			rs = pstmt.executeQuery();
			
			if( rs.next() ) {
				member = new LogonDataBean();
				member.setId(rs.getString("id"));
				member.setPasswd(rs.getString("passwd"));
				member.setName(rs.getString("name"));
				member.setJumin1(rs.getString("jumin1"));
				member.setJumin2(rs.getString("jumin2"));
				member.setEmail(rs.getString("email"));
				member.setBlog(rs.getString("blog"));
				member.setReg_date(rs.getTimestamp("reg_date"));
				member.setZipcode(rs.getString("zipcode"));
				member.setAddress(rs.getString("address"));
			}
		} catch(Exception ex) {
			ex.printStackTrace();
		} finally {
			if( rs != null ) try { rs.close(); } catch(SQLException ex) {}
			if( pstmt != null ) try { pstmt.close(); } catch(SQLException ex) {}
			if( conn != null ) try { conn.close(); } catch(SQLException ex) {}
		}
		return member;
	}
	
	
	public void updateMember(LogonDataBean member) throws Exception {
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		try{
			conn = getConnection();
			
			pstmt = conn.prepareStatement( 
					"update MEMBERS set passwd=?,name=?,email=?,blog=?,zipcode=?,address=? " 
					+ " where id=?");
			pstmt.setString(1, member.getPasswd());
			pstmt.setString(2, member.getName());
			pstmt.setString(3, member.getEmail());
			pstmt.setString(4, member.getBlog());
			pstmt.setString(5, member.getZipcode());
			pstmt.setString(6, member.getAddress());
			pstmt.setString(7,  member.getId());
			
			
			pstmt.executeUpdate();
	} catch(Exception ex){
		ex.printStackTrace();
	} finally {
		if( pstmt != null ) try { pstmt.close(); } catch(SQLException ex) {}
		if( conn != null ) try { conn.close(); } catch(SQLException ex) {}
		}
	}
	

	
	public int deleteMember(String id, String passwd) throws Exception {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String dbpasswd = "";
		int x = -1;
		
		try{
			conn = getConnection();
			
			pstmt = conn.prepareStatement( 
					"select passwd from MEMBERS where id = ?" );
			pstmt.setString(1,  id);
			rs = pstmt.executeQuery();
			
			if(rs.next() ){
				dbpasswd = rs.getString("passwd");
				if(dbpasswd.equals(passwd)) {
					pstmt = conn.prepareStatement("delete from MEMBERS where id=?");
					pstmt.setString(1,  id);
					pstmt.executeUpdate();
					x = 1; 
				} else {
					x = 0; 
				}
			}
			
		} catch(Exception ex) {
			ex.printStackTrace();
		} finally {
			if( rs != null ) try { rs.close(); } catch(SQLException ex) {}
			if( pstmt != null ) try { pstmt.close(); } catch(SQLException ex) {}
			if( conn != null ) try { conn.close(); } catch(SQLException ex) {}
		}
		return x;
	}
	
	
    public int deleteMember(String id) throws Exception {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs= null;
        //String dbpasswd="";
        int x=-1;
        
        try {
        	conn = getConnection();

            pstmt = conn.prepareStatement(
            "select passwd from MEMBERS where id = ?");
            pstmt.setString(1, id);
            rs = pstmt.executeQuery();
           
			if(rs.next()){
			//	dbpasswd= rs.getString("passwd");
				//if(dbpasswd.equals(passwd)){
					pstmt = conn.prepareStatement("delete from MEMBERS where id=?");
                    pstmt.setString(1, id);
                    pstmt.executeUpdate();
                    x= 1; //
				}else
					x= 0; //
			//}
        } catch(Exception ex) {
            ex.printStackTrace();
        } finally {
            if (rs != null) try { rs.close(); } catch(SQLException ex) {}
            if (pstmt != null) try { pstmt.close(); } catch(SQLException ex) {}
            if (conn != null) try { conn.close(); } catch(SQLException ex) {}
        }
        return x;
    }
    
    public int getMemberCount() throws Exception{
    	Connection conn = null;
    	PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		String[] column_name = {"id", "name"};
		
		int x = 0;
		
		try{
			conn = getConnection();
			pstmt = conn.prepareStatement("select count(*) from members ");
			
			rs = pstmt.executeQuery();
			
			if(rs.next() )
				x = rs.getInt(1);
		} catch(Exception ex) {
			ex.printStackTrace();
		} finally {
			if( rs != null ) try { rs.close(); } catch(SQLException ex) {}
			if( pstmt != null ) try { pstmt.close(); } catch(SQLException ex) {}
			if( conn != null ) try { conn.close(); } catch(SQLException ex) {}
		}
		return x;
    }
    
    public int getMemberCount(int n, String searchKeyword) throws Exception{
    	Connection conn = null;
    	PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		String[] column_name = {"id", "name"};
		
		int x = 0;
		
		try{
			conn = getConnection();
			pstmt = conn.prepareStatement("select count(*) from members where "+column_name[n]+" like '%" + searchKeyword + "%'");
			
			rs = pstmt.executeQuery();
			
			if(rs.next() )
				x = rs.getInt(1);
		} catch(Exception ex) {
			ex.printStackTrace();
		} finally {
			if( rs != null ) try { rs.close(); } catch(SQLException ex) {}
			if( pstmt != null ) try { pstmt.close(); } catch(SQLException ex) {}
			if( conn != null ) try { conn.close(); } catch(SQLException ex) {}
		}
		return x;
    }
    
    public List getMembers(int start, int end) throws Exception{
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List articleList = null;
		
		String[] column_name = {"id","name"};
		
		try{
			conn = getConnection();
			
			/*String sql =    "select num,writer,email,subject,passwd,reg_date,ref,re_step,re_level,content,ip,readcount,r " 
					+ "from (select num,writer,email,subject,passwd,reg_date,ref,re_step,re_level,content,ip,readcount,rownum r " 
					+ "from (select num,writer,email,subject,passwd,reg_date,ref,re_step,re_level,content,ip,readcount " 
					+ "from board order by ref desc, re_step asc) where "+column_name[n]+" like '%"+searchKeyword+"%' order by ref desc, re_step asc ) where r >= ? and r <= ?";*/
			
			String sql =    "select id,passwd,name,jumin1,jumin2,email,blog,reg_date,zipcode,address,r " 
					+ "from (select id,passwd,name,jumin1,jumin2,email,blog,reg_date,zipcode,address,rownum r " 
					+ "from (select id,passwd,name,jumin1,jumin2,email,blog,reg_date,zipcode,address " 
					+ "from members order by name asc) order by name asc ) where r >= ? and r <= ?";
			
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, start);
			pstmt.setInt(2, end);
			
			rs = pstmt.executeQuery();
			
			if( rs.next() ){
				articleList = new ArrayList(end);
				
				do{
					LogonDataBean article = new LogonDataBean();
					
					article.setId(rs.getString("id"));
					article.setPasswd(rs.getString("passwd"));
					article.setName(rs.getString("name"));
					article.setJumin1(rs.getString("jumin1"));
					article.setJumin2(rs.getString("jumin2"));
					article.setEmail(rs.getString("email"));
					article.setBlog(rs.getString("blog"));
					article.setReg_date(rs.getTimestamp("reg_date"));
					article.setZipcode(rs.getString("zipcode"));
					article.setAddress(rs.getString("address"));
					
					articleList.add(article);
				} while( rs.next() );
			}
		} catch(Exception ex) {
			ex.printStackTrace();
		} finally {
			if( rs != null ) try { rs.close(); } catch (SQLException ex) {}
			if( pstmt != null ) try { pstmt.close(); } catch (SQLException ex) {}
			if( conn != null ) try { conn.close(); } catch (SQLException ex) {}
		}
		return articleList;
	}
    
    public List getMembers(int start, int end, int n, String searchKeyword) throws Exception{
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List articleList = null;
		
		String[] column_name = {"id","name"};
		
		try{
			conn = getConnection();
			
			/*String sql =    "select num,writer,email,subject,passwd,reg_date,ref,re_step,re_level,content,ip,readcount,r " 
					+ "from (select num,writer,email,subject,passwd,reg_date,ref,re_step,re_level,content,ip,readcount,rownum r " 
					+ "from (select num,writer,email,subject,passwd,reg_date,ref,re_step,re_level,content,ip,readcount " 
					+ "from board order by ref desc, re_step asc) where "+column_name[n]+" like '%"+searchKeyword+"%' order by ref desc, re_step asc ) where r >= ? and r <= ?";*/
			
			String sql =    "select id,passwd,name,jumin1,jumin2,email,blog,reg_date,zipcode,address,r " 
					+ "from (select id,passwd,name,jumin1,jumin2,email,blog,reg_date,zipcode,address,rownum r " 
					+ "from (select id,passwd,name,jumin1,jumin2,email,blog,reg_date,zipcode,address " 
					+ "from members order by name asc) where "+column_name[n]+" like '%"+searchKeyword+"%' order by name asc ) where r >= ? and r <= ?";
			
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, start);
			pstmt.setInt(2, end);
			
			rs = pstmt.executeQuery();
			
			if( rs.next() ){
				articleList = new ArrayList(end);
				
				do{
					LogonDataBean article = new LogonDataBean();
					
					article.setId(rs.getString("id"));
					article.setPasswd(rs.getString("passwd"));
					article.setName(rs.getString("name"));
					article.setJumin1(rs.getString("jumin1"));
					article.setJumin2(rs.getString("jumin2"));
					article.setEmail(rs.getString("email"));
					article.setBlog(rs.getString("blog"));
					article.setReg_date(rs.getTimestamp("reg_date"));
					article.setZipcode(rs.getString("zipcode"));
					article.setAddress(rs.getString("address"));
					
					articleList.add(article);
				} while( rs.next() );
			}
		} catch(Exception ex) {
			ex.printStackTrace();
		} finally {
			if( rs != null ) try { rs.close(); } catch (SQLException ex) {}
			if( pstmt != null ) try { pstmt.close(); } catch (SQLException ex) {}
			if( conn != null ) try { conn.close(); } catch (SQLException ex) {}
		}
		return articleList;
	}
}